#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
Created on 2012/02/09

Module with the class that creates a database from data colected from csv files

@author: João Vitor Machado Batista nº 5479
'''

import csv
import sqlite3
import os

class createDatabase:

    filesName = []

    def tableCreation(self):
        '''
            creates the database
        '''
        
        connection = sqlite3.connect('../database/RebidesDB.db')
        c = connection.cursor()

        global filesName

        #http://stackoverflow.com/questions/1320731/count-number-of-files-with-certain-extension-in-python
        filesName = [f for f in os.listdir('../rebcsv') if f.endswith('.csv') and os.path.isfile(os.path.join('../rebcsv', f))]

        for fileN in filesName:

            fileName = '../rebcsv/{0}'.format(fileN)
            csvFile = open(fileName, 'rb')
            fileReader = csv.reader(csvFile)
            csvline1 = fileReader.next()
            csvline2 = fileReader.next()

            csvFile.close()

            year = str(csvline2[6])
            
            c.execute('''CREATE TABLE IF NOT EXISTS prof_data_year_{0} (prof_ID integer, prof_name text, prof_last_degree text,
                prof_course text, prof_category text, prof_regime text, year integer, establishment_type text,
                establishment_name text )'''.format(year))
            pass

        c.execute('''CREATE TABLE IF NOT EXISTS database_years (year text)''')
                
        connection.commit()
        connection.close()
        pass
    
    def tableFilling(self):
        '''
            fills the database with the csv content
        '''
        
        connection = sqlite3.connect('../database/RebidesDB.db')
        c = connection.cursor()

        global filesName

        for fileN in filesName:

            fileName = '../rebcsv/{0}'.format(fileN)
            csvFile = open(fileName, 'rb')
            fileReader = csv.reader(csvFile)

            csvline1 = fileReader.next()
            csvline2 = fileReader.next()

            year = str(csvline2[6])

            csvFile.close()

            csvFile = open(fileName, 'rb')
            fileReader = csv.reader(csvFile)
            
            firstLine = fileReader.next()
            fileStorage = csv.reader(csvFile, delimiter=',')

            for csvRow in fileStorage:

                c.execute('''INSERT INTO prof_data_year_{9} (prof_ID, prof_name, prof_last_degree, prof_course, prof_category,
                        prof_regime, year, establishment_type, establishment_name) values('{0}', '{1}', '{2}', '{3}',
                    '{4}', '{5}', '{6}', '{7}', '{8}')'''.\
                          format(str(csvRow[0]),str(csvRow[1]).replace('\'', '*'),str(csvRow[2]).replace('\'', '*'),
                             str(csvRow[3]).replace('\'', '*'),str(csvRow[4]).replace('\'', '*'),
                             str(csvRow[5]).replace('\'', '*'),str(csvRow[6]).replace('\'', '*'),
                             str(csvRow[7]).replace('\'', '*'),str(csvRow[8]).replace('\'', '*'),
                             year))

                pass
            
            c.execute('''INSERT INTO database_years (year) values ('{0}')'''.format(year))
            
            csvFile.close()
            
            pass

        connection.commit()
        connection.close()

        pass
